<?php
class Model_Report extends Model_Template
{
	
	public function __construct()
	{
		parent::__construct();
		$this->className = 'Model_Table_Report';

	}
	
	//////////////////////////////////////////////////////////////////////////////returns the inventoryID, productName, productQuantity needed, and productQuantity at hand of all the items that all appointments need within a start date and end date
	public function generateInventoryNeededReport($startdate,$enddate)
	{
		//Inventory needed query
		$sqlInventoryNeeded='SELECT Inventory.inventory_id, Inventory.product_name,SUM(Procedure_has_inventory.quantity_needed),Inventory.product_quantity
							FROM Inventory
							INNER JOIN Procedure_has_inventory ON
							Inventory.inventory_id=Procedure_has_Inventory.Inventory_inventory_id
							INNER JOIN Procedure_has_Appointment ON Procedure_has_Inventory.Procedure_procedure_id=Procedure_has_Appointment.Procedure_procedure_id
							INNER JOIN Appointment ON Procedure_has_Appointment.Appointment_appointment_id=Appointment.appointment_id
							WHERE Appointment.date >= ? AND Appointment.date <= ?
							GROUP BY Inventory.inventory_id;';	
		
		//prepare statement
		$inventoryNeeded = Controller_Template::$db->prepare($sqlInventoryNeeded);
		
		//execute statement
		$inventoryNeeded->execute(array($startdate,$enddate));
		
		return $inventoryNeeded->fetchAll();
	}
	
	//////////////////////////////////////////////////////////////////////////////returns the inventoryID, productName, productQuantity used of all the items that all appointments need within a start date and end date
	public function generateInventoryUsedReport($startdate,$enddate)
	{
		//Inventory used query
		$sqlInventoryUsed='SELECT Inventory.inventory_id, Inventory.product_name,SUM(Procedure_has_inventory.quantity_needed)
							FROM Inventory
							INNER JOIN Procedure_has_inventory ON
							Inventory.inventory_id=Procedure_has_Inventory.Inventory_inventory_id
							INNER JOIN Procedure_has_Appointment ON Procedure_has_Inventory.Procedure_procedure_id=Procedure_has_Appointment.Procedure_procedure_id
							INNER JOIN Appointment ON Procedure_has_Appointment.Appointment_appointment_id=Appointment.appointment_id
							WHERE Appointment.date >= ? AND Appointment.date <= ?
							AND Appointment.status="closed"
							GROUP BY Inventory.inventory_id;';
		
		//prepare statement
		$inventoryUsed = Controller_Template::$db->prepare($sqlInventoryUsed);
		
		//execute statement
		$inventoryUsed->execute(array($startdate,$enddate));
		
		return $inventoryUsed->fetchAll();
	}
	
	///////////////////////////////////////////////////////////////////////////////////returns the total number of hours spent on appointment within a startdate and enddate
	public function generateAppointmentHoursReport($startdate,$enddate)
	{
		//appointment hours query
		$sqlAppointmentHours='SELECT SUM(end_time-start_time) AS appointment_minutes, AVG(end_time-start_time) AS average_time
								FROM Appointment
								WHERE Appointment.date >= ? AND Appointment.date <= ?;';
		
		//prepare statement
		$appointmentHours=Controller_Template::$db->prepare($sqlAppointmentHours);
		
		//execute statement
		$appointmentHours->execute(array($startdate,$enddate));
		
		return $appointmentHours->fetch();
	}
	
	///////////////////////////////////////////////////////////////////////////////////returns the count of procedures performed within a startdate and enddate
	public function generateProcedurePerformedCountReport($startdate,$enddate)
	{
		//Procedure performed count query
		$sqlProcedurePerformed='SELECT Procedure.procedure_id, Procedure.name, COUNT( Procedure_has_Appointment.Procedure_procedure_id ) 
								FROM  `procedure` 
								INNER JOIN Procedure_has_Appointment ON Procedure.procedure_id = Procedure_has_Appointment.Procedure_procedure_id
								INNER JOIN Appointment ON Procedure_has_Appointment.Appointment_appointment_id = Appointment.appointment_id
								WHERE Appointment.date >= ? AND Appointment.date <= ?
								AND Appointment.status =  "closed"
								GROUP BY Procedure.procedure_id;';
		
		//prepare statement
		$procedurePerformed=Controller_Template::$db->prepare($sqlProcedurePerformed);
		
		//execute statement
		$procedurePerformed->execute(array($startdate,$enddate));
		
		return $procedurePerformed->fetchAll();
	}
}